from ..bot_plugin import Bot_plugin
from cqhttp import CQHttp
from bot_body import Bot_body
import logging
import colorful
import time
from tools.CQ_tools import group_msg_level

class Group_manager():
    def __init__(self,
        dbfile = r'./data/qqspeed.db',
        # dbfile = r'./qqspeed.db',
        tbl='qq_groups',
        primary_column='group_id'
    ):
        self.dbfile = dbfile
        self.tbl = tbl
        self.primary_column = primary_column
        self.cache=dict()
        self.init_db_test()
    def init_db_test(self):
        sql = "select * from {}".format(self.tbl)
        ret = self.execute(sql)
        if ret == None:
            raise Exception("dbfile[{}] has no table named {}".format(self.dbfile, self.tbl))

    def query(self, primary_value, column_name:str):
        '''
        return column_value in qq_groups where primary_key = primary_value;
        if not exists, return None
        '''
        if column_name not in self.cache:
            self.cache[column_name]=dict()
        if primary_value in self.cache[column_name]:
            return self.cache[column_name][primary_value]
        primary_value_in_sql = self.python_value_to_sql_value(primary_value)
        sql = "select {column_name} from {tbl} where {primary_column}={primary_value_in_sql}".format(
            primary_column=self.primary_column, tbl=self.tbl, column_name = column_name, primary_value_in_sql = primary_value_in_sql
        )
        data = self.execute(sql)
        if data == None:
            raise Exception("sql execution failed")
        if len(data)==1:
            self.cache[column_name][primary_value] = data[0][0]
        else:
            self.cache[column_name][primary_value] = None
        return self.cache[column_name][primary_value]
    def execute(self, sql:str):
        import sqlite3
        with sqlite3.connect(self.dbfile) as db:
            c = db.cursor()
            try:
                c.execute(sql)
                ans = c.fetchall()
                db.commit()
                return ans
            except:
                import traceback
                logging.warning(colorful.red("sql execute failed, sql={}\nError msg={}".format(sql, traceback.format_exc())))
                db.rollback()
                return None
    def python_value_to_sql_value(self, value):
        if type(value) == str:
            return '"{}"'.format(value)
        else:
            return value
    def update(self, primary_value, column_name:str, column_value):
        old_value = self.query(primary_value, column_name)
        new_value_in_sql = self.python_value_to_sql_value(column_value)
        primary_value_in_sql = self.python_value_to_sql_value(primary_value)
        if old_value == None: #insert
            sql = "insert into {tbl} ({primary_column}, {column_name}) values ({primary_value_in_sql}, {column_value})".format(
                primary_column=self.primary_column, tbl=self.tbl, column_name=column_name, primary_value_in_sql= primary_value_in_sql, column_value=new_value_in_sql
            )
        else:
            sql = "update {tbl} set {column_name} = {column_value} where {primary_column}={primary_value_in_sql}".format(
                primary_column=self.primary_column, tbl=self.tbl, column_name=column_name, primary_value_in_sql= primary_value_in_sql, column_value=new_value_in_sql
            )
        if self.execute(sql) != None:
            self.cache[column_name][primary_value] = column_value # query before, so could make sure column_name in self.cache
            return True
        else:
            return False

    def delete(self, primary_value, column_name:str):
        primary_value_in_sql = self.python_value_to_sql_value(primary_value)
        sql = "delete from {tbl} where {primary_column}={primary_value_in_sql}".format(
            tbl=self.tbl, primary_column=self.primary_column, primary_value_in_sql=primary_value_in_sql
        )
        if self.execute(sql) != None:
            try:
                self.cache[column_name].pop(primary_value)
            except:
                pass
            return True
        else:
            return False

    def flash_column(self, column_name:str):
        if column_name in self.cache:
            self.cache.pop(column_name)
    def flash_all(self):
        self.cache.clear()


class Plugin_authorize(Bot_plugin):
    def __init__(self, bot_body:Bot_body, group_manager:Group_manager, name="manage authorization"):
        Bot_plugin.__init__(self, bot_body, name=name)
        self.group_manager = group_manager
        self.help_msg=r"""【授权助手帮助】
0. 授权帮助：显示本条信息
1. 授权本群群号：显示本群群号【仅管理员】
2. 授权查询：显示本群授权信息【仅管理员】
3. 授权延长7天：延长若干天【仅主人】
4. 授权延长3月：延长若干月【仅主人】
5. 授权延长1年：延长若干年【仅主人】
6. 授权延长1年指定654321：延长群654321的授权一年【仅主人】
7. 授权延长1天制定0：延长所有群的授权1天【仅主人】
tips: 通过指令【授权延长0天指定xxx】可以查询xxx的到期时间
"""


    def query_end_time(self, group_id):
        '''
        return snap_end_time accrording to group_id in database
        return value:
            None:  无记录
            others: 对应的截止时间
        '''
        return self.group_manager.query(group_id,'snap_end_time')
        
    def query_have_authorization(self, group_id):
        '''
        return whether designated group have authorization.
        '''
        snap_end_time = self.query_end_time(group_id)
        if snap_end_time == None:
            return False
        return snap_end_time > time.time()
    
    def increase_end_time(self, group_id, add_secs):
        '''
        increase snap_end_time according to group_id in database;
        if group_id not in database, then add secs from current timestamp
        '''
        snap_end_time = self.query_end_time(group_id)
        if snap_end_time == None or snap_end_time < time.time():
            new_snap_end_time = int(time.time()) + add_secs
        else:
            new_snap_end_time = snap_end_time + add_secs
        return self.group_manager.update(group_id, 'snap_end_time', new_snap_end_time)
    
    def increase_end_time_for_all(self, add_secs):
        sql = "update {tbl} set {column_name} = {column_name}+{add_secs}".format(
            tbl = self.group_manager.tbl,
            column_name = 'snap_end_time',
            add_secs=add_secs
        )
        print(colorful.red("sql={}".format(sql)))
        ret = self.group_manager.execute(sql)
        self.group_manager.flash_all()
        if ret == None:
            return False
        return True

    def get_authorize_info(self, snap_end_time):
        if snap_end_time == -1:
            authorize_info = "永久"
        elif snap_end_time == 0 or snap_end_time == None:
            authorize_info = "暂无授权"
        elif snap_end_time > 0 :
            snap_end_time_local = time.localtime(snap_end_time)
            tt = time.strftime("%Y-%m-%d %H:%M:%S", snap_end_time_local)
            authorize_info = "授权有效期至{}".format(tt)
        return authorize_info

    def handle_msg(self, context):
        bot = self.bot
        raw_message = context['raw_message']
        msg_level = group_msg_level.judge(context, master_qqs=self.master_qqs)
        if raw_message == '授权帮助' and msg_level >= group_msg_level.MEMBER:
            bot.send(context, self.help_msg, auto_escape=True)
            return
        if raw_message == "授权本群群号" and msg_level >= group_msg_level.ADMIN:
            echo_msg = '【授权助手】本群群号为：{}'.format(context['group_id'])
            bot.send(context, echo_msg)
            return
        if raw_message == '授权查询' and msg_level >= group_msg_level.ADMIN:
            snap_end_time = self.query_end_time(context['group_id'])
            authorize_info = self.get_authorize_info(snap_end_time)
            bot.send(context, "本群授权信息: {}".format(authorize_info), auto_escape=True)
            return
        if raw_message[:4] == "授权延长" and msg_level >= group_msg_level.MASTER:
            p = raw_message.find("指定")
            if p > 0:
                group_id = int(raw_message[p+2:].strip())
                raw_message = raw_message[:p]
            else:
                group_id = context['group_id']
            msg = raw_message[4:].strip()
            num = int(msg[:-1])
            if msg[-1] == '年':
                num *= 365*24*3600
            elif msg[-1] == '月':
                num *= 31*24*3600
            elif msg[-1] in ['天', '日']:
                num *= 24*3600
            if group_id==0:
                ret = self.increase_end_time_for_all(num)
                if ret:
                    bot.send(context, "所有群授权统一增加{}成功".format(msg))
                else:
                    bot.send(context, "所有群授权统一增加{}失败".format(msg))
                return
            old_snap_end_time = self.query_end_time(group_id)
            old_authorize_info = self.get_authorize_info(old_snap_end_time)
            ret = self.increase_end_time(group_id, num)
            new_snap_end_time = self.query_end_time(group_id)
            new_authorize_info = self.get_authorize_info(new_snap_end_time)
            bot.send(context, "群{}授权更新结果：{}\n更新之前本群授权信息：{}\n更新之后本群授权信息：{}".format(
                group_id,
                '成功' if ret  else '失败',
                old_authorize_info, new_authorize_info), auto_escape = True)

class Plugin_suffix_manager(Bot_plugin):
    def __init__(self, bot_body:Bot_body, group_manager:Group_manager, name="manage suffixs"):
        Bot_plugin.__init__(self, bot_body, name=name)
        self.group_manager = group_manager
        self.help_msg = r"""【后缀助手帮助】
    0. 后缀帮助：显示本条信息【仅管理员】
    1. 后缀设置祝大家天天开心：设置本群截图后缀【仅管理员】
    2. 后缀全局设置祝大家事事如意：设置全局截图后缀【仅主人】
    """
        
    def query_suffix(self, group_id):
        '''
        return suffix according to group_id in database
        if accordingly suffix is "" or None:
            return global suffix( to group_id = 0)
        '''
        local_suffix = self.group_manager.query(group_id, 'suffix')
        if local_suffix == None:
            local_suffix = ""
        global_suffix = self.group_manager.query(0, 'suffix')
        if global_suffix == None:
            global_suffix = ""
        ans = local_suffix+"\n"+global_suffix
        return ans.strip()
    
    def set_suffix(self, group_id, suffix):
        return self.group_manager.update(group_id, 'suffix', suffix)
                        
    def handle_msg(self,context):
        bot = self.bot
        raw_message = context['raw_message']
        msg_level = group_msg_level.judge(context, master_qqs=self.master_qqs)
        if raw_message[:4] == '后缀设置' and msg_level>=group_msg_level.ADMIN:
            suffix = context['raw_message'][4:]
            ret = self.set_suffix(context['group_id'], suffix)
            if ret:
                bot.send(context, "已成功设置本群截图后缀为：{}".format(suffix))
            else:
                bot.send(context, "截图后缀设置失败！")
            return
        if raw_message == '后缀帮助' and msg_level >= group_msg_level.ADMIN:
            bot.send(context, self.help_msg)
            return
        if raw_message[:6] == "后缀全局设置" and msg_level >= group_msg_level.MASTER:
            suffix = context['raw_message'][6:]
            ret = self.set_suffix(0, suffix)
            if ret:
                bot.send(context, "已成功设置全局截图后缀位：{}".format(suffix))
            else:
                bot.send(context, "全局截图后缀设置失败！")
            return

                
                        



            


